"""
学生管理后台视图
"""
from rest_framework import viewsets, status, permissions
from rest_framework.decorators import action
from rest_framework.response import Response
from django.contrib.auth import get_user_model
from django.db.models import Q, Count
from django.db import transaction
from django.utils import timezone
from datetime import datetime, timedelta
import pandas as pd
import io

from accounts.permissions import IsAdminUser, IsTeacherOrAdmin
from rest_framework.pagination import PageNumberPagination

class StandardResultsSetPagination(PageNumberPagination):
    page_size = 20
    page_size_query_param = 'page_size'
    max_page_size = 100
from .models import School, Major, Grade, Student, TeacherStudentAssignment
from .serializers import (
    SchoolSerializer, MajorSerializer, GradeSerializer,
    StudentSerializer, StudentCreateSerializer, TeacherStudentAssignmentSerializer,
    TeacherSerializer
)

User = get_user_model()


class StudentManageViewSet(viewsets.ModelViewSet):
    """学生管理ViewSet"""
    
    queryset = Student.objects.all().select_related('grade').order_by('-created_at')
    serializer_class = StudentSerializer
    permission_classes = [permissions.IsAuthenticated, IsTeacherOrAdmin]
    pagination_class = StandardResultsSetPagination
    
    def get_queryset(self):
        # 根据用户角色过滤数据
        if self.request.user.role == 'teacher':
            # 老师只能看到分配给自己的学生
            queryset = Student.objects.filter(advisor=self.request.user).select_related('grade').order_by('-created_at')
        else:
            # 管理员可以看到所有学生
            queryset = self.queryset

        # 搜索过滤
        search = self.request.query_params.get('search', None)
        if search:
            queryset = queryset.filter(
                Q(student_id__icontains=search) |
                Q(name__icontains=search) |
                Q(phone__icontains=search) |
                Q(email__icontains=search)
            )

        # 校区过滤
        school_id = self.request.query_params.get('school', None)
        if school_id:
            queryset = queryset.filter(school_id=school_id)

        # 专业过滤
        major_id = self.request.query_params.get('major', None)
        if major_id:
            queryset = queryset.filter(major_id=major_id)

        # 年级过滤
        grade_id = self.request.query_params.get('grade', None) or self.request.query_params.get('grade_id', None)
        if grade_id:
            queryset = queryset.filter(grade_id=grade_id)

        # 状态过滤
        status_filter = self.request.query_params.get('status', None)
        if status_filter:
            queryset = queryset.filter(status=status_filter)

        # 就业状态过滤
        employment_status = self.request.query_params.get('employment_status', None)
        if employment_status:
            queryset = queryset.filter(employment_status=employment_status)

        # 指导老师过滤
        advisor_id = self.request.query_params.get('advisor', None)
        if advisor_id:
            queryset = queryset.filter(advisor_id=advisor_id)

        return queryset
    
    def get_serializer_class(self):
        if self.action == 'create':
            return StudentCreateSerializer
        return StudentSerializer
    
    @action(detail=False, methods=['get'])
    def statistics(self, request):
        """学生统计 - 提供完整的图表数据"""
        # 根据用户角色过滤数据
        if request.user.role == 'teacher':
            # 老师只能看到分配给自己的学生的统计
            queryset = Student.objects.filter(advisor=request.user)
        else:
            # 管理员可以看到所有学生的统计
            queryset = Student.objects.all()

        # 基础统计
        total_students = queryset.count()

        # 按学校统计 - 由于当前模型结构，暂时使用固定数据
        from students.models import School
        schools = School.objects.all()
        by_school = {}
        for school in schools:
            # 暂时将所有学生分配给第一个学校
            by_school[school.name] = queryset.count() if school == schools.first() else 0

        # 按专业统计 - 由于当前模型结构，暂时使用固定数据
        from students.models import Major
        majors = Major.objects.all()
        by_major = {}
        # 简单地将学生平均分配给各个专业
        student_count = queryset.count()
        major_count = majors.count()
        if major_count > 0:
            avg_per_major = student_count // major_count
            remainder = student_count % major_count
            for i, major in enumerate(majors):
                by_major[major.name] = avg_per_major + (1 if i < remainder else 0)

        # 按年级统计
        by_grade = dict(
            queryset.values('grade__name')
            .annotate(count=Count('id'))
            .values_list('grade__name', 'count')
        )

        # 按状态统计
        by_status = dict(
            queryset.values('status')
            .annotate(count=Count('id'))
            .values_list('status', 'count')
        )

        # 按就业状态统计
        by_employment_status = dict(
            queryset.exclude(employment_status__isnull=True)
            .exclude(employment_status='')
            .values('employment_status')
            .annotate(count=Count('id'))
            .values_list('employment_status', 'count')
        )

        # 按性别统计
        by_gender = dict(
            queryset.values('gender')
            .annotate(count=Count('id'))
            .values_list('gender', 'count')
        )

        # 最近6个月入学趋势统计
        six_months_ago = timezone.now().date() - timedelta(days=180)
        recent_enrollments = []

        # 按月统计最近6个月的入学情况
        for i in range(6):
            month_start = six_months_ago + timedelta(days=30*i)
            month_end = month_start + timedelta(days=30)

            count = queryset.filter(
                created_at__date__gte=month_start,
                created_at__date__lt=month_end
            ).count()

            recent_enrollments.append({
                'enrollment_date': month_start.strftime('%Y-%m'),
                'count': count
            })

        # 就业率计算
        graduated_students = queryset.filter(status='graduated').count()
        employed_students = queryset.filter(
            status='graduated',
            employment_status__in=['employed', 'self_employed']
        ).count()
        employment_rate = (employed_students / graduated_students * 100) if graduated_students > 0 else 0

        return Response({
            'total_students': total_students,
            'by_school': by_school,
            'by_major': by_major,
            'by_grade': by_grade,
            'by_status': by_status,
            'by_employment_status': by_employment_status,
            'by_gender': by_gender,
            'recent_enrollments': recent_enrollments,
            'employment_rate': round(employment_rate, 2)
        })
    

    
    @action(detail=False, methods=['post'])
    def batch_operation(self, request):
        """批量操作"""
        student_ids = request.data.get('student_ids', [])
        operation = request.data.get('operation')
        
        if not student_ids:
            return Response({'error': '请选择要操作的学生'}, status=status.HTTP_400_BAD_REQUEST)
        
        students = Student.objects.filter(id__in=student_ids)
        

        
        if operation == 'change_status':
            new_status = request.data.get('status')
            if not new_status:
                return Response({'error': '请提供新的学籍状态'}, status=status.HTTP_400_BAD_REQUEST)

            students.update(status=new_status)
            return Response({'message': f'成功修改{students.count()}名学生的学籍状态'})

        elif operation == 'change_employment':
            new_employment_status = request.data.get('employment_status')
            if not new_employment_status:
                return Response({'error': '请提供新的就业状态'}, status=status.HTTP_400_BAD_REQUEST)

            students.update(employment_status=new_employment_status)
            return Response({'message': f'成功修改{students.count()}名学生的就业状态'})

        elif operation == 'change_resume':
            new_resume_status = request.data.get('resume_status')
            if not new_resume_status:
                return Response({'error': '请提供新的简历状态'}, status=status.HTTP_400_BAD_REQUEST)

            students.update(resume_status=new_resume_status)
            return Response({'message': f'成功修改{students.count()}名学生的简历状态'})

        elif operation == 'delete':
            count = students.count()
            students.delete()
            return Response({'message': f'成功删除{count}名学生'})
        
        else:
            return Response({'error': '不支持的操作类型'}, status=status.HTTP_400_BAD_REQUEST)
    
    @action(detail=False, methods=['post'])
    def import_students(self, request):
        """批量导入学生"""
        from .serializers import StudentImportSerializer, StudentBatchImportSerializer
        from .models import School, Major, Grade
        from accounts.models import User
        import pandas as pd
        import uuid

        # 验证文件
        serializer = StudentImportSerializer(data=request.data)
        if not serializer.is_valid():
            return Response({'error': serializer.errors}, status=status.HTTP_400_BAD_REQUEST)

        file = serializer.validated_data['file']

        try:
            # 读取文件
            if file.name.endswith('.xlsx') or file.name.endswith('.xls'):
                df = pd.read_excel(file)
            elif file.name.endswith('.csv'):
                df = pd.read_csv(file, encoding='utf-8')
            else:
                return Response({'error': '不支持的文件格式'}, status=status.HTTP_400_BAD_REQUEST)

            # 检查必需的列
            required_columns = ['姓名', '手机号', '邮箱', '校区', '专业', '年级']
            missing_columns = [col for col in required_columns if col not in df.columns]
            if missing_columns:
                return Response({
                    'error': f'缺少必需的列: {", ".join(missing_columns)}'
                }, status=status.HTTP_400_BAD_REQUEST)

            success_count = 0
            error_count = 0
            error_list = []
            total_count = len(df)

            # 预处理：获取所有相关对象的映射
            schools_map = {school.name: school for school in School.objects.all()}
            majors_map = {major.name: major for major in Major.objects.all()}
            grades_map = {grade.name: grade for grade in Grade.objects.all()}
            teachers_map = {user.username: user for user in User.objects.filter(role='teacher')}

            for index, row in df.iterrows():
                try:
                    # 准备数据
                    row_data = {
                        'name': str(row.get('姓名', '')).strip(),
                        'password': str(row.get('密码', '123456')).strip(),
                        'gender': 'M' if str(row.get('性别', '男')).strip() in ['男', 'M', 'Male'] else 'F',
                        'birth_date': pd.to_datetime(row.get('出生日期'), errors='coerce').date() if pd.notna(row.get('出生日期')) else None,
                        'phone': str(row.get('手机号', '')).strip(),
                        'email': str(row.get('邮箱', '')).strip(),
                        'school_name': str(row.get('校区', '')).strip(),
                        'major_name': str(row.get('专业', '')).strip(),
                        'grade_name': str(row.get('年级', '')).strip(),
                        'enrollment_date': str(row.get('入学日期', '')).strip() if pd.notna(row.get('入学日期')) else '',
                        'graduation_date': str(row.get('毕业日期', '')).strip() if pd.notna(row.get('毕业日期')) else '',
                        'advisor_username': str(row.get('指导老师', '')).strip() if pd.notna(row.get('指导老师')) else '',
                        'address': str(row.get('家庭地址', '')).strip() if pd.notna(row.get('家庭地址')) else '',
                        'notes': str(row.get('备注', '')).strip() if pd.notna(row.get('备注')) else '',
                    }

                    # 验证数据
                    batch_serializer = StudentBatchImportSerializer(data=row_data)
                    if not batch_serializer.is_valid():
                        error_list.append({
                            'row': index + 2,
                            'message': f"数据验证失败: {batch_serializer.errors}"
                        })
                        error_count += 1
                        continue

                    validated_data = batch_serializer.validated_data

                    # 检查关联对象是否存在
                    school = schools_map.get(validated_data['school_name'])
                    if not school:
                        error_list.append({
                            'row': index + 2,
                            'message': f"校区 '{validated_data['school_name']}' 不存在"
                        })
                        error_count += 1
                        continue

                    major = majors_map.get(validated_data['major_name'])
                    if not major:
                        error_list.append({
                            'row': index + 2,
                            'message': f"专业 '{validated_data['major_name']}' 不存在"
                        })
                        error_count += 1
                        continue

                    grade = grades_map.get(validated_data['grade_name'])
                    if not grade:
                        error_list.append({
                            'row': index + 2,
                            'message': f"年级 '{validated_data['grade_name']}' 不存在"
                        })
                        error_count += 1
                        continue

                    # 检查指导老师（如果提供）
                    advisor = None
                    if validated_data.get('advisor_username'):
                        advisor = teachers_map.get(validated_data['advisor_username'])
                        if not advisor:
                            error_list.append({
                                'row': index + 2,
                                'message': f"指导老师 '{validated_data['advisor_username']}' 不存在"
                            })
                            error_count += 1
                            continue

                    # 检查手机号和邮箱是否已存在
                    if User.objects.filter(phone=validated_data['phone']).exists():
                        error_list.append({
                            'row': index + 2,
                            'message': f"手机号 '{validated_data['phone']}' 已被使用"
                        })
                        error_count += 1
                        continue

                    if User.objects.filter(email=validated_data['email']).exists():
                        error_list.append({
                            'row': index + 2,
                            'message': f"邮箱 '{validated_data['email']}' 已被使用"
                        })
                        error_count += 1
                        continue

                    # 生成学号（如果没有提供）
                    student_id = f"STU{uuid.uuid4().hex[:8].upper()}"
                    while Student.objects.filter(student_id=student_id).exists():
                        student_id = f"STU{uuid.uuid4().hex[:8].upper()}"

                    # 创建用户账号
                    username = f"student_{student_id.lower()}"
                    user = User.objects.create_user(
                        username=username,
                        password=validated_data['password'],
                        email=validated_data['email'],
                        phone=validated_data['phone'],
                        first_name=validated_data['name'],
                        role='student',
                        is_active=True
                    )

                    # 创建学生记录
                    student = Student.objects.create(
                        user=user,
                        student_id=student_id,
                        name=validated_data['name'],
                        gender=validated_data['gender'],
                        birth_date=validated_data.get('birth_date'),
                        phone=validated_data['phone'],
                        email=validated_data['email'],
                        school=school,
                        major=major,
                        grade=grade,
                        enrollment_date=validated_data.get('enrollment_date'),
                        graduation_date=validated_data.get('graduation_date'),
                        advisor=advisor,
                        address=validated_data.get('address'),
                        notes=validated_data.get('notes'),
                        status='active',
                        employment_status='unemployed',
                        resume_status='not_started'
                    )

                    success_count += 1

                except Exception as e:
                    error_list.append({
                        'row': index + 2,
                        'message': f"创建失败: {str(e)}"
                    })
                    error_count += 1

            return Response({
                'message': f'导入完成！成功导入 {success_count} 名学生，失败 {error_count} 条',
                'total_count': total_count,
                'success_count': success_count,
                'error_count': error_count,
                'errors': error_list[:20]  # 只返回前20个错误
            })

        except Exception as e:
            return Response({
                'error': f'文件处理失败: {str(e)}'
            }, status=status.HTTP_400_BAD_REQUEST)
    
    @action(detail=False, methods=['get'])
    def export_template(self, request):
        """导出导入模板"""
        from django.http import HttpResponse
        import pandas as pd
        from .models import School, Major, Grade
        from accounts.models import User

        # 获取示例数据
        schools = School.objects.all()
        majors = Major.objects.all()
        grades = Grade.objects.all()
        teachers = User.objects.filter(role='teacher', is_active=True)

        school_name = schools.first().name if schools.exists() else '鸥鹏科技大学'
        major_name = majors.first().name if majors.exists() else '计算机科学与技术'
        grade_name = grades.first().name if grades.exists() else '2024级'
        teacher_username = teachers.first().username if teachers.exists() else 'teacher001'

        # 创建模板数据
        template_data = {
            '姓名': ['张三', '李四'],
            '密码': ['123456', '123456'],
            '性别': ['男', '女'],
            '出生日期': ['2000-01-01', '2000-02-01'],
            '手机号': ['13800138001', '13800138002'],
            '邮箱': ['zhangsan@example.com', 'lisi@example.com'],
            '校区': [school_name, school_name],
            '专业': [major_name, major_name],
            '年级': [grade_name, grade_name],
            '入学日期': ['2024-09', '2024-09'],
            '毕业日期': ['2028-06', '2028-06'],
            '指导老师': [teacher_username, ''],
            '家庭地址': ['北京市朝阳区', '上海市浦东新区'],
            '备注': ['示例学生1', '示例学生2']
        }

        df = pd.DataFrame(template_data)

        # 创建Excel响应
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = 'attachment; filename="学生导入模板.xlsx"'

        with pd.ExcelWriter(response, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='学生信息')

            # 添加说明工作表
            instructions = pd.DataFrame({
                '字段名': ['姓名', '密码', '性别', '出生日期', '手机号', '邮箱', '校区', '专业', '年级', '入学日期', '毕业日期', '指导老师', '家庭地址', '备注'],
                '是否必填': ['是', '否', '否', '否', '是', '是', '是', '是', '是', '否', '否', '否', '否', '否'],
                '格式说明': [
                    '学生真实姓名',
                    '默认123456，可自定义',
                    '男/女，默认为男',
                    'YYYY-MM-DD格式，可为空',
                    '11位手机号码',
                    '有效邮箱地址',
                    '从系统中选择已有校区',
                    '从系统中选择已有专业',
                    '从系统中选择已有年级',
                    'YYYY-MM格式',
                    'YYYY-MM格式',
                    '老师用户名，可为空',
                    '详细家庭地址，可为空',
                    '备注信息，可为空'
                ],
                '示例': [
                    '张三',
                    '123456',
                    '男',
                    '2000-01-01',
                    '13800138000',
                    'zhangsan@example.com',
                    school_name,
                    major_name,
                    grade_name,
                    '2024-09',
                    '2028-06',
                    teacher_username,
                    '北京市朝阳区',
                    '优秀学生'
                ]
            })
            instructions.to_excel(writer, index=False, sheet_name='填写说明')

        return response


class TeacherViewSet(viewsets.ReadOnlyModelViewSet):
    """老师管理ViewSet（只读，用于下拉选择）"""

    queryset = User.objects.filter(role='teacher', is_active=True).order_by('username')
    serializer_class = TeacherSerializer
    permission_classes = [permissions.IsAuthenticated, IsTeacherOrAdmin]
    pagination_class = None  # 不分页，返回所有老师

    def get_queryset(self):
        """获取老师列表"""
        queryset = self.queryset

        # 搜索过滤
        search = self.request.query_params.get('search', None)
        if search:
            queryset = queryset.filter(
                Q(username__icontains=search) |
                Q(first_name__icontains=search) |
                Q(last_name__icontains=search) |
                Q(email__icontains=search)
            )

        return queryset
